<?php
namespace Home\Controller;
class ExcelController extends AdminController{

	public function get_g_l(){
        if(IS_POST){
            $where = array();
            $g_name = I('g_name','','trim');
            if($g_name){
                $where['g_name'] = array('LIKE',"%$g_name%") ;
            }
            $fields = array(
            'top_goods.g_id',
            'top_goods.g_num',
            'top_goods.g_name',
            'top_goods.g_yjs',
            'top_goods.g_fzr',
            'top_goods.g_zl',
            'top_goods.g_bz',
            'top_goods.g_addtime',
            'top_goods.g_updatetime',
            'top_goods_dw.dw_name',
            );
            $db = M('Goods');
            $g_list = $db->field($fields)->join('LEFT JOIN top_goods_dw ON top_goods_dw.dw_id = top_goods.g_dw_id')
                    ->where($where)->order('g_num desc,g_id')->select();
            $this->goods_export($g_list);
        }else{
            $this->display();
        }
    }

    public function get_c_l(){
        if(IS_POST){
            $where = array();
            $b_id = I('b_id',0,'intval');
            $g_name = I('g_name','','trim');
            $c_db = M('Goods_cate');
            $c_list = $c_db->select();
            $cate = new \Home\Common\Category;
            if($b_id){
                $b_ids = $cate->getChildsId($c_list,$b_id);         
                if($b_ids){
                    $b_ids[] = $b_id ;
                    $where['top_goods_count.bid'] = array('IN',$b_ids) ;
                }else{
                    $where['top_goods_count.bid'] = $b_id;
                }
            }
            if($g_name){
                $where['g_name'] = array('LIKE',"%$g_name%") ;
            }
            $fields = array(
            'top_goods_count.gid',
            'top_goods_count.gcount',
            'top_goods.g_name',
            'top_goods.g_zl',
            'top_goods_cate.name',
            'top_goods_dw.dw_name',
            );
            $db = M('Goods_count');
            $g_list = $db->field($fields)->join('LEFT JOIN top_goods ON top_goods_count.gid = top_goods.g_id')
                    ->join('LEFT JOIN top_goods_cate ON top_goods_cate.id = top_goods_count.bid')
                    ->join('LEFT JOIN top_goods_dw ON top_goods_dw.dw_id = top_goods.g_dw_id')
                    ->where($where)->order('g_num desc,g_id')->select();
            $this->count_export($g_list);
        }else{
            $c_db = M('Goods_cate');
            $c_list = $c_db->select();
            $cate = new \Home\Common\Category;
            $c_list = $cate->unlimitedForLevel($c_list);
            $this->assign('c_list',$c_list);
            $this->display();
        }
    }
    //导出出入库记录
    public function get_l_l(){
        if(IS_POST){
            $where = array();
            $b_id = I('b_id',0,'intval');
            $g_name = I('g_name','','trim');
            $aid = I('aid',1,'intval');
            $begintime = I('begintime');
            $endtime = I('endtime');
            $c_db = M('Goods_cate');
            $c_list = $c_db->select();
            $cate = new \Home\Common\Category;

            if($b_id){
                $b_ids = $cate->getChildsId($c_list,$b_id);         
                if($b_ids){
                    $b_ids[] = $b_id ;
                    $where['top_goods_log.bid'] = array('IN',$b_ids) ;
                }else{
                    $where['top_goods_log.bid'] = $b_id;
                }
            }

            if($g_name){
                $where['g_name'] = array('LIKE',"%$g_name%") ;
            }
            
            if($begintime && $endtime){
                $begintime = strtotime($begintime);
                $endtime = strtotime($endtime);
                $where['d_time'] = array('BETWEEN',array($begintime, $endtime));
            }elseif($begintime){
                $begintime = strtotime($begintime);
                $where['d_time'] = array('EGT', $begintime);
            }elseif($endtime){
                $endtime = strtotime($endtime);
                $where['d_time'] = array('ELT', $endtime);
            }

            $where['top_goods_log.aid'] = $aid;
            $where['top_goods_log.ischeck'] = 1;

            $fields = array(
            'top_goods_log.gid',
            'top_goods.g_name',
            'top_goods_cate.name',
            'top_goods_dw.dw_name',
            'top_goods.g_zl',
            'top_goods_log.aid',
            'top_w_action.a_info',
            'top_goods_log.gcount',
            'top_goods_log.d_time',
            'top_goods_log.doer',
            'top_goods_log.c_time',
            'top_goods_log.checker',            
            );
            $db = M('goods_log');
            $l_list = $db->field($fields)->join('LEFT JOIN top_goods ON top_goods_log.gid = top_goods.g_id')
                    ->join('LEFT JOIN top_w_action ON top_goods_log.action = top_w_action.a_id')
                    ->join('LEFT JOIN top_goods_cate ON top_goods_cate.id = top_goods_log.bid')
                    ->join('LEFT JOIN top_goods_dw ON top_goods_dw.dw_id = top_goods.g_dw_id')
                    ->where($where)->order('g_num desc,gid')->select();
            // dump($l_list);exit;
            $data = array();
            $l_count =array();
            foreach ($l_list as $k=>$v){
                $data[$k]['g_name'] = $v['g_name'];
                $data[$k]['name'] = $v['name'];
                $data[$k]['dw_name'] = $v['dw_name'];
                $data[$k]['g_zl'] = $v['g_zl'] == 0 ? '未知' : $v['g_zl'];
                $data[$k]['aid'] = $v['aid'] == 1?'入库' : '出库';
                $data[$k]['a_info'] = $v['a_info'];
                $data[$k]['gcount'] = $v['gcount'];
                $data[$k]['d_time'] = date('Y年m月d日',$v['d_time']);
                $data[$k]['doer'] = $v['doer'];
                $data[$k]['c_time'] = date('Y年m月d日',$v['c_time']);
                $data[$k]['checker'] = $v['checker'];
                $l_count[$v['gid']]['g_name'] = $v['g_name'];
                $l_count[$v['gid']]['action'] = $data[$k]['aid'];
                $l_count[$v['gid']]['gcount'] += $v['gcount'];
            }
            $c_name = 'l_l'.time();
            S($c_name,$data,300);
            $this->assign('l_count',$l_count);
            $this->assign('c_name',$c_name);
            $this->display('get_l_c');
            
        }else{
            $c_db = M('Goods_cate');
            $c_list = $c_db->select();
            $cate = new \Home\Common\Category;
            $c_list = $cate->unlimitedForLevel($c_list);
            $this->assign('c_list',$c_list);
            $this->display();
        }
    }

    public function download(){
        $c_name = I('post.c_name','','trim');
        $data = S($c_name);
        if($data){
            $this->logs_export($data);
        }else{
            $this->error('找不到导出的数据，请尝试重新导出！');
        }
    }

    //导入数据页面
    public function import_g(){
        if(IS_POST){
            header("Content-Type:text/html;charset=utf-8");
            $upload = new \Think\Upload();// 实例化上传类
            $upload->exts      =     array('xls', 'xlsx');// 设置附件上传类
            // 上传文件
            $info   =   $upload->uploadOne($_FILES['excelData']);
            $filename = './Uploads/'.$info['savepath'].$info['savename'];
            $exts = $info['ext'];
            // print_r($info);exit;
            if(!$info) {// 上传错误提示错误信息
                  $this->error($upload->getError());
              }else{// 上传成功
                      $this->goods_import($filename, $exts);
            }
        }else{
            $this->display('');
        }       
    }

    //导入数据方法
    protected function goods_import($filename, $exts='xls'){
        //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
        import("Org.Util.PHPExcel");
        //创建PHPExcel对象，注意，不能少了\
        $PHPExcel=new \PHPExcel();
        //如果excel文件后缀名为.xls，导入这个类
        if($exts == 'xls'){
            import("Org.Util.PHPExcel.Reader.Excel5");
            $PHPReader=new \PHPExcel_Reader_Excel5();
        }else if($exts == 'xlsx'){
            import("Org.Util.PHPExcel.Reader.Excel2007");
            $PHPReader=new \PHPExcel_Reader_Excel2007();
        }


        //载入文件
        $PHPExcel=$PHPReader->load($filename);
        //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
        $currentSheet=$PHPExcel->getSheet(0);
        //获取总列数
        $allColumn=$currentSheet->getHighestColumn();
        //获取总行数
        $allRow=$currentSheet->getHighestRow();
        $data = array();
        //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
        for($currentRow=2;$currentRow<=$allRow;$currentRow++){
            //从哪列开始，A表示第一列
            for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){
                //数据坐标
                $address=$currentColumn.$currentRow;
                //读取到的数据，保存到数组$arr中
                $cell =$currentSheet->getCell($address)->getValue();
                //$cell = $data[$currentRow][$currentColumn];
                if($cell instanceof PHPExcel_RichText){
                    $cell  = $cell->__toString();
                }
                // print_r($cell);
                switch ($currentColumn){
                case A:
                    $data[$currentRow]['g_num'] = intval($cell);
                    break;
                case B:
                    $data[$currentRow]['g_name'] = $cell;
                    break;
                case C:
                    $data[$currentRow]['g_dw_id'] = M('goods_dw')->where(array('name' => $cell))->getField('dw_id');
                    if(!$data[$currentRow]['g_dw_id']){
                        $data[$currentRow]['g_dw_id'] = 1;
                    }
                    break;
                case D:
                    $data[$currentRow]['g_yjs'] = intval($cell);
                    break;
                case E:
                    $data[$currentRow]['g_zl'] = $cell=='未知'? 0:intval($cell);
                    break;
                case F:
                    $data[$currentRow]['g_bz'] = $cell?$cell:'';
                    break;
                default:
                    break;
                }
            }
        }
        //dump($data);
        $g_db = D('Goods');
        $s_count = 0 ;
        foreach ($data as $v) {
            if($v['g_name']){
                if($g_db->create($v)){
                    $g_db->add();
                    $s_count++;
                }
            }
            
        }
        if($s_count > 0){
            $this->success("成功导入".$s_count."条数据",U('Goods/showlist'));
        }else{
            $this->error("导入失败");
        }
        //$this->save_import($data);
    }

    //导入数据页面
    public function import_c(){
        if(IS_POST){
            header("Content-Type:text/html;charset=utf-8");
            $upload = new \Think\Upload();// 实例化上传类
            $upload->exts      =     array('xls', 'xlsx');// 设置附件上传类
            // 上传文件
            $info   =   $upload->uploadOne($_FILES['excelData']);
            $filename = './Uploads/'.$info['savepath'].$info['savename'];
            $exts = $info['ext'];
            // print_r($info);exit;
            if(!$info) {// 上传错误提示错误信息
                  $this->error($upload->getError());
              }else{// 上传成功
                      $this->count_import($filename, $exts);
            }
        }else{
            $this->display('');
        }       
    }

    //批量入库
    protected function count_import($filename, $exts='xls')
    {
        //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
        import("Org.Util.PHPExcel");
        //创建PHPExcel对象，注意，不能少了\
        $PHPExcel=new \PHPExcel();
        //如果excel文件后缀名为.xls，导入这个类
        if($exts == 'xls'){
            import("Org.Util.PHPExcel.Reader.Excel5");
            $PHPReader=new \PHPExcel_Reader_Excel5();
        }else if($exts == 'xlsx'){
            import("Org.Util.PHPExcel.Reader.Excel2007");
            $PHPReader=new \PHPExcel_Reader_Excel2007();
        }


        //载入文件
        $PHPExcel=$PHPReader->load($filename);
        //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
        $currentSheet=$PHPExcel->getSheet(0);
        //获取总列数
        $allColumn=$currentSheet->getHighestColumn();
        //获取总行数
        $allRow=$currentSheet->getHighestRow();
        $data = array();
        //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
        for($currentRow=2;$currentRow<=$allRow;$currentRow++){
            //从哪列开始，A表示第一列
            for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){
                //数据坐标
                $address=$currentColumn.$currentRow;
                //读取到的数据，保存到数组$arr中
                $cell =$currentSheet->getCell($address)->getValue();
                //$cell = $data[$currentRow][$currentColumn];
                if($cell instanceof PHPExcel_RichText){
                    $cell  = $cell->__toString();
                }
                // print_r($cell);
                switch ($currentColumn){
                case A:
                    $data[$currentRow]['gid'] = M('goods')->where(array('g_name' => $cell))->getField('g_id');
                    break;
                case B:
                    $data[$currentRow]['bid'] = M('goods_cate')->where(array('name' => $cell))->getField('id');
                    if(!$data[$currentRow]['bid']){
                        $data[$currentRow]['bid'] = 1;
                    }
                    break;
                case C:
                    $data[$currentRow]['gcount'] = intval($cell);
                    break;
                default:
                    break;
                }
            }
        }
        // dump($data);exit;
        $c_db = M('goods_count');
        $s_count = 0 ;
        foreach ($data as $v) {
            if($v['gid'] && $v['gcount'] > 0){
                $result = $c_db->where(array('gid' =>$v['gid'],'bid' =>$v['bid']))->find();
                if($result){
                    $result['gcount'] = $result['gcount'] + $v['gcount'];
                    $c_db->save($result);
                    $s_count++;
                }else{
                    $c_db->add($v);
                    $s_count++;
                }
            }
        }
        if($s_count > 0){
            $this->success("成功导入".$s_count."条数据",U('Wareroom/wlist'));
        }else{
            $this->error("导入失败");
        }
        //$this->save_import($data);
    }

    //导出数据方法
    protected function goods_export($goods_list=array())
    {
        // print_r($goods_list);exit;
        $goods_list = $goods_list;
        $data = array();
        foreach ($goods_list as $k=>$goods_info){
            $data[$k]['g_id'] = $goods_info['g_id'];
            $data[$k]['g_num'] = $goods_info['g_num'];
            $data[$k]['g_name'] = $goods_info['g_name'];
            $data[$k]['dw_name'] = $goods_info['dw_name'];
            $data[$k]['g_yjs'] = $goods_info['g_yjs'];
            $data[$k]['g_zl'] = $goods_info['g_zl'] == 0 ? '未知' : $goods_info['g_zl'];
            $data[$k]['g_bz'] = $goods_info['g_bz'];
            $data[$k]['g_addtime'] = date('Y年m月d日',$goods_info['g_addtime']);
            $data[$k]['g_updatetime'] = date('Y年m月d日',$goods_info['g_updatetime']);
            $data[$k]['g_fzr'] = $goods_info['g_fzr'];
        }

        //print_r($goods_list);
        // dump($data);exit;

        foreach ($data as $field=>$v){
            if($field == 'g_id'){
                $headArr[]='货物编号';
            }
            if($field == 'g_num'){
                $headArr[]='排序';
            }

            if($field == 'g_name'){
                $headArr[]='货物名称';
            }

            if($field == 'dw_name'){
                $headArr[]='货物单位';
            }

            if($field == 'g_yjs'){
                $headArr[]='库存预警';
            }

            if($field == 'g_zl'){
                $headArr[]='货物单个重量';
            }

            if($field == 'g_bz'){
                $headArr[]='备注信息';
            }
            if($field == 'g_addtime'){
                $headArr[]='录入时间';
            }

            if($field == 'g_updatetime'){
                $headArr[]='更新时间';
            }

            if($field == 'g_fzr'){
                $headArr[]='负责人';
            }
        }

        $filename="goods_list";

        $this->getExcel($filename,$headArr,$data);
    }

    //导出库存列表方法
    protected function count_export($goods_list=array())
    {
        // print_r($goods_list);exit;
        $goods_list = $goods_list;
        $data = array();
        foreach ($goods_list as $k=>$goods_info){
            $data[$k]['gid'] = $goods_info['gid'];
            $data[$k]['g_name'] = $goods_info['g_name'];
            $data[$k]['name'] = $goods_info['name'];
            $data[$k]['gcount'] = $goods_info['gcount'];
            $data[$k]['dw_name'] = $goods_info['dw_name'];
            $data[$k]['g_zl'] = $goods_info['g_zl'] == 0 ? '未知' : $goods_info['g_zl'];
        }

        //print_r($goods_list);
        // dump($data);exit;

        foreach ($data as $field=>$v){
            if($field == 'gid'){
                $headArr[]='货物编号';
            }

            if($field == 'g_name'){
                $headArr[]='货物名称';
            }

            if($field == 'name'){
                $headArr[]='所属仓库';
            }

            if($field == 'gcount'){
                $headArr[]='货物库存数量';
            }

            if($field == 'dw_name'){
                $headArr[]='货物单位';
            }

            
            if($field == 'g_zl'){
                $headArr[]='货物单个重量';
            }
        }

        $filename="count_list";

        $this->getExcel($filename,$headArr,$data);
    }


    //导出数据方法
    protected function logs_export($data = array())
    {
        $data = $data;       
        // dump($data);exit;

        foreach ($data as $field=>$v){
            if($field == 'g_name'){
                $headArr[]='货物名称';
            }
            if($field == 'name'){
                $headArr[]='所属仓库';
            }

            if($field == 'dw_name'){
                $headArr[]='货物单位';
            }

            if($field == 'g_zl'){
                $headArr[]='货物单个重量';
            }

            if($field == 'aid'){
                $headArr[]='出入行为';
            }

            if($field == 'a_info'){
                $headArr[]='出入原因';
            }

            if($field == 'gcount'){
                $headArr[]='出入数量';
            }
            
            if($field == 'd_time'){
                $headArr[]='申请时间';
            }

            if($field == 'doer'){
                $headArr[]='申请人';
            }

            if($field == 'c_time'){
                $headArr[]='审核时间';
            }

            if($field == 'checker'){
                $headArr[]='审核人';
            }
        }

        $filename="logs_list";

        $this->getExcel($filename,$headArr,$data);
    }

    private  function getExcel($fileName,$headArr,$data){
        //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
        import("Org.Util.PHPExcel");
        import("Org.Util.PHPExcel.Writer.Excel5");
        import("Org.Util.PHPExcel.IOFactory.php");

        $date = date("Y_m_d",time());
        $fileName .= "_{$date}.xls";

        //创建PHPExcel对象，注意，不能少了\
        $objPHPExcel = new \PHPExcel();
        $objProps = $objPHPExcel->getProperties();

        //设置表头
        $key = ord("A");
        //print_r($headArr);exit;
        foreach($headArr as $v){
            $colum = chr($key);
            $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
            $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
            $key += 1;
        }

        $column = 2;
        $objActSheet = $objPHPExcel->getActiveSheet();

        //print_r($data);exit;
        foreach($data as $key => $rows){ //行写入
            $span = ord("A");
            foreach($rows as $keyName=>$value){// 列写入
                $j = chr($span);
                $objActSheet->setCellValue($j.$column, $value);
                $span++;
            }
            $column++;
        }

        $fileName = iconv("utf-8", "gb2312", $fileName);

        //重命名表
        //$objPHPExcel->getActiveSheet()->setTitle('test');
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output'); //文件通过浏览器下载
        exit;
    }
}